﻿Option Strict Off

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Text.RegularExpressions
Imports DataModel

Public Class Import

  Public Sub ImportSessions(ByVal conference As ConferenceListModel.Conference, ByVal verifyLinks As Boolean)
    Try
      BuildListOpenXML(IO.Path.Combine(My.Application.Info.DirectoryPath, "PDC09\PDC09SessionSchedule.xlsx"), conference, verifyLinks)


    Catch ex As Exception

    End Try

  End Sub
#Region "Open XML"
  ' Given a workbook file, a sheet name, and row and column integer values, retrieve the value of the cell.
  ' Call the function like this to retrieve the value from cell B3:
  ' Dim value As String = XLGetCellValueRowCol("Sample.xlsx", "Sheet1", 2, 3)
  Public Function XLGetCellValueRowCol(ByVal fileName As String, ByVal sheetName As String, ByVal colNumber As Integer, ByVal row As Integer) As String
    ' The challenge: Convert a cell number into a letter name.
    Return XLGetCellValueRowCol(fileName, sheetName, GetColumnName(colNumber), row)
  End Function

  Private Function GetColumnName(ByVal column As Integer) As String
    ' This algorithm was found here:
    ' http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa

    ' Given a column number, retrieve the corresponding
    ' column string name:
    Dim value As Integer
    Dim remainder As Integer
    Dim result As String = String.Empty
    value = column

    Do While (value > 0)
      remainder = (value - 1) Mod 26
      result = Chr(65 + remainder) & result
      value = CInt(Int((value - remainder) / 26))
    Loop
    Return result
  End Function

  ' Given a workbook file, a sheet name, and row and column names, retrieve the value of the cell.
  ' Call the function like this:
  ' Dim value As String = XLGetCellValueRowCol("Sample.xlsx", "Sheet1", "B", 3)
  Public Function XLGetCellValueRowCol(ByVal document As SpreadsheetDocument, ByVal sheetName As String, ByVal colName As String, ByVal row As Integer) As String
    Dim value As String = Nothing


    Dim wbPart As WorkbookPart = document.WorkbookPart

    ' Find the sheet with the supplied name, and then use that Sheet object
    ' to retrieve a reference to the appropriate worksheet.
    Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)(). _
      Where(Function(s) s.Name = sheetName).FirstOrDefault()

    If theSheet Is Nothing Then
      Throw New ArgumentException("sheetName")
    End If

    ' Retrieve a reference to the worksheet part, and then use its Worksheet property to get 
    ' a reference to the cell whose address matches the address you've supplied:
    Dim wsPart As WorksheetPart = CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
    Dim theCell As Cell = wsPart.Worksheet.Descendants(Of Cell). _
      Where(Function(c) c.CellReference = colName & row.ToString()).FirstOrDefault

    ' If the cell doesn't exist, return an empty string.
    If theCell IsNot Nothing Then
      value = theCell.InnerText

      ' If the cell represents an integer number, you're done. 
      ' For dates, this code returns the serialized value that 
      ' represents the date. The code handles strings and booleans
      ' individually. For shared strings, the code looks up the corresponding
      ' value in the shared string table. For booleans, the code converts 
      ' the value into the words TRUE or FALSE.
      If theCell.DataType IsNot Nothing Then
        Select Case theCell.DataType.Value
          Case CellValues.SharedString
            ' For shared strings, look up the value in the shared strings table.
            Dim stringTable = wbPart.GetPartsOfType(Of SharedStringTablePart).FirstOrDefault()
            ' If the shared string table is missing, something's wrong.
            ' Just return the index that you found in the cell.
            ' Otherwise, look up the correct text in the table.
            If stringTable IsNot Nothing Then
              value = stringTable.SharedStringTable.ElementAt(Integer.Parse(value)).InnerText
            End If
          Case CellValues.Boolean
            Select Case value
              Case "0"
                value = "FALSE"
              Case Else
                value = "TRUE"
            End Select
        End Select
      End If
    End If
    Return value
  End Function

#End Region

  Private Sub BuildListOpenXML(ByVal xlsxPath As String, ByVal conference As ConferenceListModel.Conference, ByVal verifyLinks As Boolean)

    ' Open the document as read-only.
    Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(xlsxPath, False)
    'Dim ls = New Generic.List(Of Session)

    Using (document)
      Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.Descendants(Of Sheet)()
      If (sheets.Count() = 0) Then
        ' The specified worksheet does not exist.
        Return
      End If

      Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First.Id), WorksheetPart)

      ' Get the cells in the specified column and order them by row.
      Dim rows As IEnumerable(Of Row) = worksheetPart.Worksheet.Descendants(Of Row)()

      If (rows.Count() = 0) Then
        ' The specified column does not exist.
        Return
      End If
      For Each r As Row In rows
        If CInt(r.RowIndex.Value.ToString) > 2 Then
          Dim title = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "B", CInt(r.RowIndex.Value.ToString))
          Dim code = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "A", CInt(r.RowIndex.Value.ToString))
          Dim description = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "C", CInt(r.RowIndex.Value.ToString))
          Dim sessiondate = Date.FromOADate(XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "E", CInt(r.RowIndex.Value.ToString))).ToString("MM/dd/yyyy")

          Dim sessiontime = Date.FromOADate(XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "F", CInt(r.RowIndex.Value.ToString))).ToString("t")
          If Not code.StartsWith("BOF") AndAlso code.Length > 0 AndAlso title.Length > 0 Then

            Dim s As New ConferenceListModel.Session
            s.SessionID = Guid.NewGuid
            s.ConferenceID = conference.ConferenceID
            s.SessionTitle = title
            s.SessionCode = code
            s.SessionDTTM = sessiondate & " " & sessiontime
            s.SessionDescription = description
            s.SessionURL = conference.RootSessionURL & code
            s.WMVHighMediaURL = Helpers.Resources.GetResourceURL(conference.RootVideoURL & "wmvhigh/", code, "wmv", "WMVHIGH", verifyLinks)
            s.WMVMediaURL = Helpers.Resources.GetResourceURL(conference.RootVideoURL & "wmv/", code, "wmv", "WMV", verifyLinks)
            s.MP4MediaURL = Helpers.Resources.GetResourceURL(conference.RootVideoURL & "mp4/", code, "mp4", "MP4", verifyLinks)
            s.PowerPointMediaURL = Helpers.Resources.GetResourceURL(conference.RootVideoURL & "ppt/", code, "pptx", "PPTX", verifyLinks)

            conference.Sessions.Add(s)

          End If

        End If

      Next
    End Using

  End Sub

End Class
